
from sqlalchemy.orm import *
from sqlalchemy.types import * 
from sqlalchemy import create_engine
from typing import Optional
from sqlalchemy import select
import rich
class Base(DeclarativeBase):
    def _asdict(self):
        return {column.name: getattr(self, column.name) for column in self.__table__.columns}

class user(Base):
    __tablename__="user"
    user_id : Mapped[Integer] = mapped_column( Integer, nullable=False,primary_key=True, autoincrement=True  )
    username : Mapped[String] = mapped_column( String,  ) 
    password : Mapped[String] = mapped_column( String,  ) 
    email : Mapped[String] = mapped_column( String,  ) 
    pic : Mapped[String] = mapped_column( String,  )
    user_group_id : Mapped[Integer] = mapped_column( Integer )



def login(engine,body):
    with Session(engine) as session:
        record = session.query(user.__table__).filter_by(username=body['username']).first()
        if record is not None:
            record = record._asdict()
            if record['password'] == body['password']:
                return record
        return None


def select_all(engine):
    with Session(engine) as session :
        return [rec._asdict() for rec in session.query(user).all()]
    
def select_by_col(engine,cond:dict):
    with Session(engine) as session :
        return [rec._asdict() for rec in session.query(user).filter_by(**cond).all()]


def update_one(engine,cond:dict,item:dict):
    with Session(engine) as session:
        record = session.query(user).filter_by(**cond).first()
        if record:
            # Update the record with the new values
            for key, value in item.items():
                setattr(record, key, value)
            session.commit()
            return True
        return False
    
def add_one(engine,item:dict):
    try:
        with Session(engine) as session :
            record = user(**item)
            session.add(record)
            session.commit()
            return True 
    except Exception as e:
        rich.print(f"[red] {e}")
        return False
    
def delete_one(engine,cond:dict):
    try:
        with Session(engine) as session :
            record = session.query(user).filter_by(id=cond['id']).first()
            session.delete(record)
            session.commit()
            return True 
    except Exception as e:
        rich.print(f"[red] {e}")
        return False  
def delete_batch(engine,cond:dict):
    try:
        with Session(engine) as session :
            records = session.query(user).filter_by(**cond).all()
            for rec in records:
                session.delete(rec)
            session.commit()
            return True 
    except Exception as e:
        rich.print(f"[red] {e}")
        return False


def set_group(engine, body):
    try:
        with Session(engine) as session:
            group_id = body['user_group_id']
            user_id = body['user_id']
            record = session.query(user).filter_by(user_id=user_id)
            if record:
                record.user_group_id = group_id
                return [record._asdict()]
            raise ValueError("[red] user group id not found")
    except Exception as e:
        rich.print(f"[red] {e}")
